<?php
include '../library/config.php';
include '../library/connect.php';

//request 3 latest movies
$topmovie = mysql_query("SELECT m.movieID, m.movieName, m.movieImage, CONCAT(SUBSTRING(m.movieDesc,1,150),'...') as 'movieDesc', AVG(r.rating) as 'rating', COUNT(r.rating) as 'total'
FROM p1_Movies as m left outer join p1_ratings as r
ON m.movieID = r.movieID
ORDER BY m.releaseDate
LIMIT 2");

//request 3 toprated
$ratedmovie = mysql_query("SELECT m.movieID, m.movieName, m.movieImage, CONCAT(SUBSTRING(m.movieDesc,1,150),'...') as 'movieDesc', AVG(r.rating) as 'rating', COUNT(r.rating) as 'total'
FROM p1_Movies as m join p1_ratings as r
ON m.movieID = r.movieID
ORDER BY rating DESC
LIMIT 2");

?>
<?php
//last 3 movies
while($row = mysql_fetch_array( $topmovie )) {
	// Print out the contents of each row
	$movieid = $row['m.movieID'];
	$moviename = $row['m.movieName'];
	$movieimage = $row['m.movieImage'];
	$moviedesc = $row['m.movieDesc'];
	$rating = round($row['rating'],2);
	$rtotal = $row['total'];
	
	echo '<div class="movies"> <div class="movieimg"><a href="movies.php?movieid='. $movieid .'"><img src="'.$movieimage.'" alt="Click Here" border="0" /></a></div><div class="movierating">Rating: '.$rating.' by '.$rtotal.' users</div> <div class="movietitle"><a href="movies.php?movieid='. $movieid .'">'.$moviename.'</a></div><div class="movietext">'.$moviedesc.' <a href="movies.php?movieid='. $movieid .'">Read more</a></div></div>';
}
?>
<?php
//3 top rated
while($row = mysql_fetch_array( $ratedmovie )) {
	// Print out the contents of each row
	$movieid = $row['m.movieID'];
	$moviename = $row['m.movieName'];
	$movieimage = $row['m.movieImage'];
	$moviedesc = $row['m.movieDesc'];
	$rating = round($row['rating'],2);
	$rtotal = $row['total'];
	
	echo '<div class="movies"> <div class="movieimg"><a href="movies.php?movieid='. $movieid .'"><img src="'.$movieimage.'" alt="Click Here" border="0" /></a></div><div class="movierating">Rating: '.$rating.' by '.$rtotal.' users</div> <div class="movietitle"><a href="movies.php?movieid='. $movieid .'">'.$moviename.'</a></div><div class="movietext">'.$moviedesc.' <a href="movies.php?movieid='. $movieid .'">Read more</a></div></div>';
}
?>

<?php
$movieid = mysql_real_escape_string($_GET['movieid']);

include '../library/config.php';
include '../library/connect.php';

$movie =  mysql_query("SELECT p.movieID, p.movieName, p.movieFLV, p.movieDesc, a.actorName, a.actorWiki, p.releaseDate
FROM p1_Movies as p LEFT OUTER JOIN p1_actors as a
ON p.movieDirector = a.actorID
WHERE p.movieID='$movieid'");

$actors = mysql_query("SELECT a.actorName, a.actorWiki
FROM p1_MoAC as m INNER JOIN p1_Actors as a
ON m.actorID = a.actorID
WHERE m.movieID = '$movieid'");

$rating = mysql_query("SELECT AVG(rating) as 'rating', COUNT(rating) as 'total', userID
FROM p1_Ratings as r
WHERE  r.movieID = '$movieid'");

include '../library/disconnect.php';
?>